﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Data.OleDb;
using OgilvyOne.IDAL;

namespace OgilvyOne.DALCore
{
    /// <summary>
    /// 对数据库形式的数据访问操作
    /// </summary>
    public class DBService : IDBService
    {
        public DBService(string connString)
        { this.ConnString = connString; }

        #region IDBService Members

        /// <summary>
        /// 执行无返回数据集的存储过程
        /// </summary>
        /// <param name="procName">存储过程的名称</param>
        /// <param name="parms">参数集合</param>
        /// <returns>存储过程影响的数据行数</returns>
        public int ExecuteStoredProcedure(string procName, OleDbParameter[] parms)
        {
            object result = DoDBOperation(delegate
              {
                  OleDbCommand cmd = GetCommand(procName, parms);
                  cmd.Connection = connection;
                  int i = cmd.ExecuteNonQuery();
                  return i;
              }
          );
            return (int)result;
        }

        /// <summary>
        /// 执行有DataSet返回的存储过程
        /// </summary>
        /// <param name="procName">存储过程的名称</param>
        /// <param name="parms">参数集合</param>
        /// <returns>执行存储过程得到的数据集</returns>
        public DataSet ExecuteStoredProcedureDataSet(string procName, OleDbParameter[] parms)
        {
            object result = DoDBOperation(delegate
            {
                OleDbCommand cmd = GetCommand(procName, parms);
                cmd.Connection = connection;
                using (System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    ds.Tables.Add(dt);
                    return ds;
                }
            }
                );
            return (DataSet)result;
        }

        /// <summary>
        /// 得到存储过程对应的Command
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parms">参数列表</param>
        /// <returns>存储过程对应的Command</returns>
        OleDbCommand GetCommand(string procName, OleDbParameter[] parms)
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            if (parms != null)
            {
                foreach (OleDbParameter parm in parms)
                {
                    if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Input) && (parm.Value == null))
                        parm.Value = DBNull.Value;
                    cmd.Parameters.Add(parm);
                }
            }
            return cmd;
        }

        /// <summary>
        /// 执行Sql语句返回数据记录
        /// 如果执行过程中有异常,则抛出;
        /// 如果查询结果为空,则OleDbDataReader为空
        /// </summary>
        /// <param name="queryString">要执行的Sql语句列表</param>
        /// <returns>返回记录</returns>
        public OleDbDataReader GetDataReader(string queryString)
        {
            object result = DoDBOperation(delegate
            {
                OleDbCommand dc = new OleDbCommand(queryString, connection);
                OleDbDataReader dr = dc.ExecuteReader();
                return dr;
            }
            );
            return (OleDbDataReader)result;
        }
        /// <summary>
        /// 执行Sql语句返回首行首列数据
        /// 如果执行过程中有异常,则抛出;
        /// 如果查询结果为空,则对象为空
        /// </summary>
        /// <param name="queryString">要执行的Sql语句列表</param>
        /// <returns>某数据类型</returns>
        public object GetScalar(string queryString)
        {
            object result = DoDBOperation(delegate
            {
                OleDbCommand dc = new OleDbCommand(queryString, connection);
                object obj = dc.ExecuteScalar();
                return obj;
            }
           );
            return result;
        }

        #endregion



        #region ICommonQueryService Members

        string mConnString = string.Empty;
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnString
        {
            get { return mConnString; }
            set { this.mConnString = value; }
        }

        #region 无返回的Sql

        /// <summary>
        /// 执行一条没有返回值的Sql语句
        /// </summary>
        /// <param name="queryString">要执行的Sql语句</param>
        public int ExecuteNonQuery(string queryString)
        {
          
          object RowCount = DoDBOperation(delegate
            {
                OleDbCommand cmd = new OleDbCommand(queryString, connection);
                int recordCount = cmd.ExecuteNonQuery();
                return recordCount;
            });
            return (int)RowCount;
        }

        /// <summary>
        /// 执行一组没有返回值的Sql语句
        /// </summary>
        /// <param name="queryString">要执行的一组Sql</param>
        public void ExecuteNonQuery(string[] queryString)
        {
            DoDBOperation(delegate
            {
                OleDbCommand cmd = new OleDbCommand();
                OleDbTransaction trans = connection.BeginTransaction();
                cmd.Connection = connection;
                cmd.Transaction = trans;
                try
                {
                    foreach (string sql in queryString)
                    {
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();
                    }
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
                return "";
            });
        }

        #endregion

        #region 有返回的Sql

        /// <summary>
        /// 执行Sql语句返回数据集
        /// 如果执行过程中有异常,则抛出;
        /// 如果查询结果为空,则DataSet为初始化过的空集合
        /// </summary>
        /// <param name="queryString">要执行的Sql</param>
        /// <returns>
        /// 返回数据集
        /// </returns>
        public DataSet GetDataSet(string queryString)
        {
            object result = DoDBOperation(delegate
             {
                 DataSet ds = new System.Data.DataSet();
                 OleDbDataAdapter adapter = new OleDbDataAdapter(queryString, connection);
                 adapter.Fill(ds);
                 return ds;
             }
              );
            return (DataSet)result;
        }

        /// <summary>
        /// 执行若干条Sql语句,获得数据集
        /// 如果执行过程中有异常,则抛出;
        /// 如果查询结果为空,则DataSet为初始化过的空集合
        /// </summary>
        /// <param name="queryString">要执行的Sql语句列表</param>
        /// <returns>
        /// 返回数据集
        /// </returns>
        public DataSet GetDataSet(string[] queryString)
        {
            object result = DoDBOperation(delegate
            {
                DataSet ds = new DataSet();
                foreach (string sql in queryString)
                {
                    OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connection);
                    DataTable table = new DataTable();
                    adapter.Fill(table);
                    ds.Tables.Add(table);
                }
                return ds;
            }
             );
            return (DataSet)result;
        }
       

        #endregion

        #endregion

        OleDbConnection connection = null;

        #region 打开/关闭数据库连接,执行数据库操作

        /// <summary>
        /// 要执行的具体代码的代理
        /// </summary>
        /// <returns>可能的返回值</returns>
        delegate object DoQuery();

        /// <summary>
        /// 统一执行数据库操作的地方,已经处理了连接/断开连接的操作
        /// </summary>
        /// <param name="queryFunction">对数据库的具体操作代码的代理入口</param>
        /// <returns>数据库操作返回值</returns>
        object DoDBOperation(DoQuery queryFunction)
        {
            object result = null;
            if (OpenConnection())
            {//如果连接数据库成功(Lukcy!)
                try
                {//执行指定的代理操作
                    result = queryFunction();
                }
                catch (Exception ex)
                {
                    throw new Exception("执行Sql语句出错:" + ex.Message);
                }
                finally
                {//无论结果怎样,我们还是得尝试着关闭连接
                    CloseConnection();
                }
            }
            else//如果连接数据库失败,抛出异常,希望上层不要因此崩溃了:(
                throw new Exception("连接数据库失败");

            return result;
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <returns>是否成功打开连接</returns>
        bool OpenConnection()
        {
            bool connSucc = false;
            if (ConnString != string.Empty && ConnString.Trim() != "")
            {
                try
                {
                    connection = new OleDbConnection(ConnString);
                    connection.Open();
                    connSucc = true;
                }
                catch { }
            }
            return connSucc;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <returns>是否成功关闭连接</returns>
        bool CloseConnection()
        {
            bool closeSucc = false;
            if (connection != null && connection.State == ConnectionState.Open)
            {
                connection.Close();
                closeSucc = true;
            }
            return closeSucc;
        }

        #endregion


    }
}
